package cn.dida.dao;

import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.core.convert.support.DefaultConversionService;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import cn.dida.model.Pageable;
import cn.dida.model.SalesNewMan;
import cn.dida.model.Salesman;
import cn.dida.model.SalesmanSaleHistory;

/**
 * 促销员管理 数据交互类
 * @author weiwei
 */
@Repository
public class SalesmanDao extends DaoBase {

	/**
	 * 促销员列表
	 * @param companyId 公司id
	 * @param keyword 查询条件
	 * @param state 状态（1：正常、2：已注销）
	 * @param page 分页游标
	 * @return Pageable<Salesman> 促销员分页列表数据
	 */
    public Pageable<SalesNewMan> query(Integer companyId, String keyword, String branchkeyword, String adname,Integer state, int page) {
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer(" select count(1) from salesman s join branch h on s.id = h.create_salesman_id  ");
        StringBuffer sqlQueryResult = new StringBuffer(" select s.id,s.`code`,s.`name`,s.phone,h.`name` branchname,h.adname,s.psnname,s.state,s.create_time createTime from salesman s join branch h on s.id = h.create_salesman_id  ");
       
        StringBuffer sqlWhere = new StringBuffer(" where s.company_id = ? ");
        args = ArrayUtils.add(args, companyId);
//        args = ArrayUtils.add(args, companyId);
        if (state != null) {
            sqlWhere.append(" and s.state = ?");
            args = ArrayUtils.add(args, state);
        }
        if (keyword != null) {
            sqlWhere.append(" and (s.name like ? or s.phone like ? )");
            args = ArrayUtils.add(args, "%" + keyword + "%");
            args = ArrayUtils.add(args, "%" + keyword + "%");
        }
        if (branchkeyword != null) {
            sqlWhere.append(" and h.`name` like ? ");
            args = ArrayUtils.add(args, "%" + branchkeyword + "%");
        }
        if (adname != null) {
            sqlWhere.append(" and h.adname like ? ");
            args = ArrayUtils.add(args, "%" + adname + "%");
        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by s.create_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<SalesNewMan> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(SalesNewMan.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 促销员列表（数据导出用）
     * @param companyId 所属公司id
     * @param keyword   查询条件
     * @param state     促销员状态：1正常，2注销
     * @return List<Salesman> 促销员列表
     * @date 2016-12-08 11:46
     */
    public List<SalesNewMan> queryForExport(Integer companyId, String keyword,  String branchkeyword, String adname,Integer state) {
    	 Object[] args = {};
    	 StringBuffer sqlQueryResult = new StringBuffer(" select s.id,s.`code`,s.`name`,s.phone,h.`name` branchname,h.adname,s.psnname,s.state,s.create_time createTime from salesman s join branch h on s.id = h.create_salesman_id  ");
         
         StringBuffer sqlWhere = new StringBuffer(" where s.company_id = ? ");
         args = ArrayUtils.add(args, companyId);
         if (state != null) {
             sqlWhere.append(" and s.state = ?");
             args = ArrayUtils.add(args, state);
         }
         if (keyword != null) {
             sqlWhere.append(" and (s.name like ? or s.phone like ?)");
             args = ArrayUtils.add(args, "%" + keyword + "%");
             args = ArrayUtils.add(args, "%" + keyword + "%");
         }
         if (branchkeyword != null) {
             sqlWhere.append(" and h.`name` like ? ");
             args = ArrayUtils.add(args, "%" + branchkeyword + "%");
         }
         if (adname != null) {
             sqlWhere.append(" and h.adname like ? ");
             args = ArrayUtils.add(args, "%" + adname + "%");
         }
         // 查询记录
         sqlQueryResult.append(sqlWhere).append("  order by s.create_time desc ");
         List<SalesNewMan> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(SalesNewMan.class));
         return result;
    }
    /**
     * 促销员列表
     * @param companyId 公司id
     * @param state 促销员状态：1正常，2注销
     * @return List<Salesman> 促销员列表数据
     */
    public List<Salesman> query(Integer companyId, Integer state) {
        String sql = "select s.*,dpt.name 'departmentName' from salesman s join department dpt on s.department_id = dpt.id where s.company_id = ? and s.state = ?";
        List<Salesman> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Salesman.class), companyId, state);
        return result;
    }
    /**
     * 新增促销员
     * @param salesman 促销员实体类
     * @return int 受影响的记录行数
     */
    public int insert(Salesman salesman) {
        String sql = "insert into salesman(company_id, name, phone, department_id, position, scan_code_count, remark, state, create_time) values(:company_id, :name, :phone, :department_id, :position, :scan_code_count, :remark, :state, :create_time)";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("company_id", salesman.getCompanyId());
        paramMap.put("name", salesman.getName());
        paramMap.put("phone", salesman.getPhone());
        paramMap.put("department_id", salesman.getDepartmentId());
        paramMap.put("position", salesman.getPosition());
        paramMap.put("scan_code_count", 0);
        paramMap.put("remark", salesman.getRemark());
        paramMap.put("state", salesman.getState());
        paramMap.put("create_time", new Date());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }
    /**
     * 更新促销员
     * @param salesman 促销员实体类
     * @return int 受影响的记录行数
     */
    public int update(SalesNewMan salesman) {
        String sql = "update salesman set name=:name, phone=:phone,state=:state,approve_time=:approve_time,staff_id=:staff_id,psnname=:psnname where company_id = :company_id and id = :id ";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("id", salesman.getId());
        paramMap.put("company_id", salesman.getCompanyId());
        paramMap.put("name", salesman.getName());
        paramMap.put("phone", salesman.getPhone());
        paramMap.put("state", salesman.getState());
        paramMap.put("approve_time", salesman.getApproveTime());
        paramMap.put("staff_id", salesman.getStaffId());
        paramMap.put("psnname", salesman.getPsnname());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }
    
    public Integer updatebranch(SalesNewMan salesman) {
		String sql = "update branch set name=:name,adname=:adname, adcode=:adcode,address=:address where create_salesman_id = :id ";
		HashMap<String, Object> paramMap = new HashMap<>();
		paramMap.put("id", salesman.getId());
		paramMap.put("name", salesman.getBranchname());
		paramMap.put("adcode", salesman.getAdcode());
		paramMap.put("adname", salesman.getAdname());
		paramMap.put("address", salesman.getAddress());
		return namedParameterJdbcTemplate.update(sql, paramMap);
	}

    /**
     * 获取促销员信息
     * @param companyId 公司id
     * @param id 促销员id
     * @return Salesman 促销员信息
     */
    public SalesNewMan get(Integer companyId, Integer id) {
        String sql = "select s.id,s.`code`,s.`name`,s.phone,s.psnname,h.`name` branchname,h.adname,h.adcode,h.address,s.state,s.create_time createTime from salesman s join branch h on s.id = h.create_salesman_id  where s.company_id = ? and s.id = ?";
        try {
        	SalesNewMan salesman = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SalesNewMan.class), companyId, id);
            return salesman;
        } catch (DataAccessException e) {
            return null;
        }
    }
    /**
     * 获取促销员信息
     * @param companyId 公司id
     * @param phone 促销员电话
     * @return Salesman 促销员信息
     */
    public Salesman getByMobile(Integer companyId, String phone) {
        String sql = "select s.* from salesman s where s.company_id = ? and s.phone = ?";
        try {
            Salesman salesman = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Salesman.class), companyId, phone);
            return salesman;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 删除促销员
     * @param companyId 公司id
     * @param id 促销员id
     * @return int 受影响的记录行数
     */
    public int delete(Integer companyId, Integer id) {
        String sql = "delete from salesman where company_id = ? and id = ?";
        return jdbcTemplate.update(sql, companyId, id);
    }

    /**
     * 销售记录列表
     * @param salesmanId 促销员id
     * @param keyword 查询条件
     * @param timeLimitStart 扫码开始时间
     * @param timeLimitEnd 扫码结束时间
     * @param page 分页游标
     * @return Pageable<SalesmanSaleHistory> 销售记录分页列表
     */
    public Pageable<SalesmanSaleHistory> querySaleHistory(Integer salesmanId, String keyword, Long timeLimitStart, Long timeLimitEnd, int page) {
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from log_scancode_salesman");
        StringBuffer sqlQueryResult = new StringBuffer("select * from log_scancode_salesman");

        StringBuffer sqlWhere = new StringBuffer(" where salesman_id = ?");
        args = ArrayUtils.add(args, salesmanId);
        if (StringUtils.isNotBlank(keyword)) {
            sqlWhere.append(" and (pro_name  like ? or pro_barcode = ? or pro_number = ?)");
            args = ArrayUtils.add(args, "%" + keyword + "%");
            args = ArrayUtils.add(args, keyword);
            args = ArrayUtils.add(args, keyword);
        }
        if (timeLimitStart != null) {
            sqlWhere.append(" and c_time >= ?");
            args = ArrayUtils.add(args, timeLimitStart);
        }
        if (timeLimitEnd != null) {
            sqlWhere.append(" and c_time <= ?");
            args = ArrayUtils.add(args, timeLimitEnd);
        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        BeanPropertyRowMapper<SalesmanSaleHistory> rowMapper = new BeanPropertyRowMapper<>(SalesmanSaleHistory.class);

        DefaultConversionService conversionService = (DefaultConversionService) rowMapper.getConversionService();
        conversionService.addConverter(Long.class, Date.class, source -> new Date(source));

        List<SalesmanSaleHistory> result = jdbcTemplate.query(sqlQueryResult.toString(), args, rowMapper);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 促销员销售记录列表（数据导出用）
     * @param salesmanId     促销员id
     * @param keyword        查询条件
     * @param timeLimitStart 扫码开始时间
     * @param timeLimitEnd   扫码结束时间
     * @return List<SalesmanSaleHistory> 促销员销售记录列表
     * @date 2016-12-08 13:51
     */
    public List<SalesmanSaleHistory> querySaleHistoryForExport(Integer salesmanId, String keyword, Long timeLimitStart, Long timeLimitEnd) {
        Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("select * from log_scancode_salesman");

        StringBuffer sqlWhere = new StringBuffer(" where salesman_id = ?");
        args = ArrayUtils.add(args, salesmanId);
        if (StringUtils.isNotBlank(keyword)) {
            sqlWhere.append(" and (pro_name  like ? or pro_barcode = ? or pro_number = ?)");
            args = ArrayUtils.add(args, "%" + keyword + "%");
            args = ArrayUtils.add(args, keyword);
            args = ArrayUtils.add(args, keyword);
        }
        if (timeLimitStart != null) {
            sqlWhere.append(" and c_time >= ?");
            args = ArrayUtils.add(args, timeLimitStart);
        }
        if (timeLimitEnd != null) {
            sqlWhere.append(" and c_time <= ?");
            args = ArrayUtils.add(args, timeLimitEnd);
        }

        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c_time desc ");
        BeanPropertyRowMapper<SalesmanSaleHistory> rowMapper = new BeanPropertyRowMapper<>(SalesmanSaleHistory.class);

        DefaultConversionService conversionService = (DefaultConversionService) rowMapper.getConversionService();
        conversionService.addConverter(Long.class, Date.class, source -> new Date(source));

        List<SalesmanSaleHistory> result = jdbcTemplate.query(sqlQueryResult.toString(), args, rowMapper);
        return result;
    }
    
    
    /**
     * 未支付列表
     * @author kelan
     * @date 2017年4月27日
     * @param companyid
     * @param keyword
     * @param timeLimitStart
     * @param timeLimitEnd
     * @param page
     * @return
     */
    public Pageable<Map<String, Object>> querysalepaymentyList(Integer companyid, String keyword, Long timeLimitStart, Long timeLimitEnd, int page){
    	 Object[] args = {};
         StringBuffer sqlQueryResultCount = new StringBuffer();
         
         sqlQueryResultCount.append(" select count(1) from (SELECT  ");
		 sqlQueryResultCount.append("	log.product_id,p.`name` proname,p.`number` procode,count(log.product_id) nubs  ");
		 sqlQueryResultCount.append(" FROM	log_scancode_salesman log,  ");
		 sqlQueryResultCount.append(" product p,   ");
		 sqlQueryResultCount.append("	salesman m ,  ");
		 sqlQueryResultCount.append("	company y "); 
		 sqlQueryResultCount.append(" WHERE "); 
		 sqlQueryResultCount.append(" log.salesman_id = m.id ");
		 sqlQueryResultCount.append(" and m.company_id = y.id ");
		 sqlQueryResultCount.append(" and p.id = log.product_id  and log.payment_state = 0  and log.weixin_openid is not null    and  log.weixin_appid is not null     ");
		
         StringBuffer sqlQueryResult = new StringBuffer();
         sqlQueryResult.append(" select * from (SELECT  ");
         sqlQueryResult.append("	log.product_id,p.`name` proname,p.`number` procode,count(log.product_id) nubs  ");
         sqlQueryResult.append(" FROM	log_scancode_salesman log,  ");
         sqlQueryResult.append(" product p,   ");
         sqlQueryResult.append("	salesman m ,  ");
         sqlQueryResult.append("	company y "); 
         sqlQueryResult.append(" WHERE "); 
         sqlQueryResult.append(" log.salesman_id = m.id ");
         sqlQueryResult.append(" and m.company_id = y.id ");
         sqlQueryResult.append(" and p.id = log.product_id  and log.payment_state = 0  and log.weixin_openid is not null    and  log.weixin_appid is not null     ");
        

         StringBuffer sqlWhere = new StringBuffer(" and  y.id  = ? ");
         args = ArrayUtils.add(args, companyid);
         if (StringUtils.isNotBlank(keyword)) {
             sqlWhere.append(" and (p.name  like ? or p.number = ?)");
             args = ArrayUtils.add(args, "%" + keyword + "%");
             args = ArrayUtils.add(args, keyword);
         }
         if (timeLimitStart != null) {
             sqlWhere.append(" and log.c_time >= ?");
             args = ArrayUtils.add(args, timeLimitStart);
         }
         if (timeLimitEnd != null) {
             sqlWhere.append(" and log.c_time <= ?");
             args = ArrayUtils.add(args, timeLimitEnd);
         }
         StringBuffer sqlWhereToal = new StringBuffer();
         sqlWhereToal.append(sqlWhere);
         sqlWhereToal.append(" group by  log.product_id ) b");
         
         // 查询记录总数
         Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhereToal).toString(), Integer.class, args);
         // 查询记录
         sqlWhere.append("  group by  log.product_id  ");
         sqlQueryResult.append(sqlWhere).append("  ORDER BY p.number desc  limit ?,? ) b ");
         args = ArrayUtils.add(args, (page - 1) * getPageSize());
         args = ArrayUtils.add(args, getPageSize());
         List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
         return new Pageable<>(page, getPageSize(), resultTotal, result);
         
    }
    
    /**
     * 促销员未支付记录导出
     * @author kelan
     * @date 2017年5月5日
     * @param salesmanId
     * @param keyword
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public List<Map<String, Object>> querySalepaymentyExport(Integer companyid, String keyword, Long timeLimitStart, Long timeLimitEnd) {
    	Object[] args = {};
        
		
        StringBuffer sqlQueryResult = new StringBuffer();
        sqlQueryResult.append(" select * from (SELECT  ");
        sqlQueryResult.append("	m.`name` salesname,log.product_id,p.`name` proname,p.`number` procode,count(log.product_id) nubs  ");
        sqlQueryResult.append(" FROM	log_scancode_salesman log,  ");
        sqlQueryResult.append(" product p,   ");
        sqlQueryResult.append("	salesman m ,  ");
        sqlQueryResult.append("	company y "); 
        sqlQueryResult.append(" WHERE "); 
        sqlQueryResult.append(" log.salesman_id = m.id ");
        sqlQueryResult.append(" and m.company_id = y.id ");
        sqlQueryResult.append(" and p.id = log.product_id  and log.payment_state = 0 ");
       

        StringBuffer sqlWhere = new StringBuffer(" and  y.id  = ? ");
        args = ArrayUtils.add(args, companyid);
        if (StringUtils.isNotBlank(keyword)) {
            sqlWhere.append(" and (p.name  like ? or p.number = ?)");
            args = ArrayUtils.add(args, "%" + keyword + "%");
            args = ArrayUtils.add(args, keyword);
        }
        if (timeLimitStart != null) {
            sqlWhere.append(" and log.c_time >= ?");
            args = ArrayUtils.add(args, timeLimitStart);
        }
        if (timeLimitEnd != null) {
            sqlWhere.append(" and log.c_time <= ?");
            args = ArrayUtils.add(args, timeLimitEnd);
        }
        StringBuffer sqlWhereToal = new StringBuffer();
        sqlWhereToal.append(sqlWhere);
        sqlWhereToal.append(" group by  log.product_id ) b");
        
        // 查询记录
        sqlWhere.append("  group by  log.product_id, log.salesman_id  ");
        sqlQueryResult.append(sqlWhere).append("  ORDER BY p.number desc  ) b ");
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return result;
    }
    
    /**
     * 未支付详情
     * @author kelan
     * @date 2017年4月27日
     * @param companyid
     * @param price
     * @param product_id
     * @return
     */
    public Pageable<Map<String, Object>> querysalepaymentydetailList(Integer companyid,String keyword, String companyname, String price, Integer product_id,int page){
        Object[] args = {};
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) from (SELECT log.salesman_id, m.`name` salename,y.id company_id,y.`name` company_name,log.product_id,p.`name` proname,  ");
		sql.append("p.`number` procode,log.payment_state,count(log.product_id) nubs,CONCAT('"+price+"','') price ,CONCAT('"+price+"'*count(log.product_id),'') sumprice         ");
		sql.append("FROM log_scancode_salesman log,product p,salesman m,company y                                                     ");
		sql.append("WHERE log.salesman_id = m.id  AND m.company_id = y.id                                                             ");
		sql.append("AND p.id = log.product_id AND y.id = "+companyid+"                                                                          ");
		sql.append("and log.product_id = "+product_id+"   and log.payment_state = '0'   and log.weixin_openid is not null    and  log.weixin_appid is not null         ");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and m.`name` like '%"+keyword+"%' ");
		}
		if(StringUtils.isNotBlank(companyname)){
			sql.append(" and y.`name` like '%"+companyname+"%' ");
		}
		sql.append("group by log.product_id,log.salesman_id        ) b                                                                   ");
		Integer resultTotal = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
		
		sql = new StringBuffer();
		sql.append("SELECT GROUP_CONCAT(DISTINCT(log.id)) tableid,log.salesman_id,log.weixin_openid openid,log.weixin_appid appid, m.`name` salename,y.id company_id,y.`name` company_name,log.product_id,p.`name` proname,  ");
		sql.append("p.`number` procode,log.payment_state,count(log.product_id) nubs,CONCAT('"+price+"','') price ,CONCAT(round('"+price+"' * count(log.product_id),2 ),'') sumprice1         ");
		sql.append("FROM log_scancode_salesman log,product p,salesman m,company y                                                     ");
		sql.append("WHERE log.salesman_id = m.id  AND m.company_id = y.id                                                             ");
		sql.append("AND p.id = log.product_id AND y.id = "+companyid+"                                                                          ");
		sql.append("and log.product_id = "+product_id+"   and log.payment_state = '0'  and log.weixin_openid is not null  and  log.weixin_appid is not null     ");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and m.`name` like '%"+keyword+"%' ");
		}
		if(StringUtils.isNotBlank(companyname)){
			sql.append(" and y.`name` like '%"+companyname+"%' ");
		}
		sql.append("group by log.product_id,log.salesman_id                                                                           ");
        sql.append(" order by log.c_time desc limit ?,?  ");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sql.toString(), args);
        
        return  new Pageable<>(page, getPageSize(), resultTotal, result);
        
   }
    
    /**
     * 更新促销员扫码记录中的状态
     * @author kelan
     * @date 2017年4月28日
     * @param salesman_id
     * @param product_id
     */
    public void updatelogscancodesalesmanstate(Integer salesman_id, String product_id,Long newtime,Float price,String id) {
		// TODO Auto-generated method stub
		
		String sql = "UPDATE log_scancode_salesman SET payment_state = 1 , payment_time = '"+newtime+"',payment_price = '"+price+"' where product_id = ? and id in ("+id+") and salesman_id = ?  ";
		
		jdbcTemplate.update(sql, product_id,salesman_id);
		
		
	}
    
    
    /**
     * 插入支付记录
     * @author kelan
     * @date 2017年4月28日
     * @param js
     */
    public void inerstlogscancodesalesmanpayment(JSONArray js,Long newtime) {
		// TODO Auto-generated method stub
		
    	String sql = "INSERT INTO `log_scancode_salepayment` ( `salesman_id`, `sales_company_id`, `product_id`, `scancode_nubs`, `product_price`, `product_sumprice`, `payment_state`, `c_time`,`weixin_openid`,`message_id`,`weixin_appid`) VALUES ";
    	StringBuffer suffix = new StringBuffer(); 
    	for(Object jo :js){
    		JSONObject genjo= JSONObject.parseObject(jo.toString());
    		suffix.append("( '" +Integer.parseInt( genjo.getString("salesman_id").toString()) + "','"+Integer.parseInt(genjo.getString("company_id").toString())+ "' ,'"+Integer.parseInt(genjo.getString("product_id").toString())+"'"
		    		+ ",'"+Integer.parseInt( genjo.getString("nubs").toString()) +"','"+Float.parseFloat( genjo.getString("price").toString())+"','" +Float.parseFloat( genjo.getString("sumprice").toString()) + "',"
		    				+ "0,'"+newtime+"','"+genjo.getString("openid").toString()+"','"+genjo.getString("message_id").toString()+"','"+genjo.getString("appid").toString()+"'),");  
    	}
	    String sql1 = sql + suffix.substring(0, suffix.length() - 1);  
	    jdbcTemplate.execute(sql1);
	}
    
   
    /**
     * 已支付列表
     * @author kelan
     * @date 2017年4月28日
     * @param companyid
     * @param keyword
     * @param timeLimitStart
     * @param timeLimitEnd
     * @param page
     * @return
     */
    public Pageable<Map<String, Object>> querysalepaymentyoffList(Integer companyid, String keyword, Long timeLimitStart, Long timeLimitEnd, int page){
    	 Object[] args = {};
         StringBuffer sqlQueryResultCount = new StringBuffer();
         
         sqlQueryResultCount.append(" select count(1) from (SELECT  ");
		 sqlQueryResultCount.append("	log.product_id,p.`name` proname,p.`number` procode,sum(log.scancode_nubs) nubs,  ");
		 sqlQueryResultCount.append("	log.product_price,sum(log.product_sumprice) sumprice,log.c_time,log.payment_state  ");
		 sqlQueryResultCount.append(" FROM	log_scancode_salepayment log,  ");
		 sqlQueryResultCount.append(" product p,   ");
		 sqlQueryResultCount.append("	salesman m ,  ");
		 sqlQueryResultCount.append("	company y "); 
		 sqlQueryResultCount.append(" WHERE "); 
		 sqlQueryResultCount.append(" log.salesman_id = m.id ");
		 sqlQueryResultCount.append(" and m.company_id = y.id ");
		 sqlQueryResultCount.append(" and p.id = log.product_id  ");
		
         StringBuffer sqlQueryResult = new StringBuffer();
         sqlQueryResult.append(" select * from (SELECT  ");
         sqlQueryResult.append("	log.product_id,p.`name` proname,p.`number` procode,sum(log.scancode_nubs) nubs,  ");
         sqlQueryResult.append("	CONCAT(log.product_price,'') product_price,CONCAT(round(sum(log.product_sumprice),2),'') sumprice,log.c_time,log.payment_state  ");
         sqlQueryResult.append(" FROM	log_scancode_salepayment log,  ");
         sqlQueryResult.append(" product p,   ");
         sqlQueryResult.append("	salesman m ,  ");
         sqlQueryResult.append("	company y "); 
         sqlQueryResult.append(" WHERE "); 
         sqlQueryResult.append(" log.salesman_id = m.id ");
         sqlQueryResult.append(" and m.company_id = y.id ");
         sqlQueryResult.append(" and p.id = log.product_id  ");
        

         StringBuffer sqlWhere = new StringBuffer(" and  y.id  = ? ");
         args = ArrayUtils.add(args, companyid);
         if (StringUtils.isNotBlank(keyword)) {
             sqlWhere.append(" and (p.name  like ? or p.number = ?)");
             args = ArrayUtils.add(args, "%" + keyword + "%");
             args = ArrayUtils.add(args, keyword);
         }
         if (timeLimitStart != null) {
             sqlWhere.append(" and log.c_time >= ?");
             args = ArrayUtils.add(args, timeLimitStart);
         }
         if (timeLimitEnd != null) {
             sqlWhere.append(" and log.c_time <= ?");
             args = ArrayUtils.add(args, timeLimitEnd);
         }
         StringBuffer sqlWhereToal = new StringBuffer();
         sqlWhereToal.append(sqlWhere);
         sqlWhereToal.append(" group by  log.product_id,log.c_time  ORDER BY log.c_time desc  ) b");
         
         // 查询记录总数
         Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhereToal).toString(), Integer.class, args);
         // 查询记录
         sqlWhere.append(" group by  log.product_id,log.c_time  ");
         sqlQueryResult.append(sqlWhere).append("  ORDER BY log.c_time desc   limit ?,? ) b ");
         args = ArrayUtils.add(args, (page - 1) * getPageSize());
         args = ArrayUtils.add(args, getPageSize());
         List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
         return new Pageable<>(page, getPageSize(), resultTotal, result);
         
    }
    
    /**
     * 已支付促销员产品数据导出
     * @author kelan
     * @date 2017年5月5日
     * @param companyid
     * @param keyword
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public List<Map<String, Object>> querySalepaymentOffExport(Integer companyid, String keyword, Long timeLimitStart, Long timeLimitEnd) {
    	Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer();
        sqlQueryResult.append(" select * from (SELECT  ");
        sqlQueryResult.append("	m.`name` salesname,log.product_id,p.`name` proname,p.`number` procode,sum(log.scancode_nubs) nubs, log.weixin_openid,log.weixin_appid,   ");
        sqlQueryResult.append("	CONCAT(log.product_price,'') product_price,CONCAT(sum(log.product_sumprice),'') sumprice, FROM_UNIXTIME(log.c_time/1000) c_time,log.payment_state,log.weixin_payreturn_msg   ");
        sqlQueryResult.append(" FROM	log_scancode_salepayment log,  ");
        sqlQueryResult.append(" product p,   ");
        sqlQueryResult.append("	salesman m ,  ");
        sqlQueryResult.append("	company y "); 
        sqlQueryResult.append(" WHERE "); 
        sqlQueryResult.append(" log.salesman_id = m.id ");
        sqlQueryResult.append(" and m.company_id = y.id ");
        sqlQueryResult.append(" and p.id = log.product_id  ");
       

        StringBuffer sqlWhere = new StringBuffer(" and  y.id  = ? ");
        args = ArrayUtils.add(args, companyid);
        if (StringUtils.isNotBlank(keyword)) {
            sqlWhere.append(" and (p.name  like ? or p.number = ?)");
            args = ArrayUtils.add(args, "%" + keyword + "%");
            args = ArrayUtils.add(args, keyword);
        }
        if (timeLimitStart != null) {
            sqlWhere.append(" and log.c_time >= ?");
            args = ArrayUtils.add(args, timeLimitStart);
        }
        if (timeLimitEnd != null) {
            sqlWhere.append(" and log.c_time <= ?");
            args = ArrayUtils.add(args, timeLimitEnd);
        }
        // 查询记录
        sqlWhere.append(" group by  log.product_id,log.c_time ,log.salesman_id ");
        sqlQueryResult.append(sqlWhere).append("  ORDER BY log.c_time desc ) b ");
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return result;
    }
    
   
    /**
     * 已支付详情
     * @author kelan
     * @date 2017年4月28日
     * @param companyid
     * @param keyword
     * @param companyname
     * @param price
     * @param product_id
     * @param page
     * @return
     */
    public Pageable<Map<String, Object>> querysalepaymentyoffdetailList(Integer companyid,String keyword, String companyname, Integer product_id,String c_time ,int page){
        Object[] args = {};
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) from (SELECT log.salesman_id, m.`name` salename,y.id company_id,y.`name` company_name,log.product_id,p.`name` proname,   ");
		sql.append("p.`number` procode,log.payment_state,log.scancode_nubs nubs, CONCAT(log.product_price,'') price,CONCAT(log.product_sumprice,'') sumprice ,log.c_time       ");
		sql.append("FROM log_scancode_salepayment log,product p,salesman m,company y                                                     ");
		sql.append("WHERE log.salesman_id = m.id  AND m.company_id = y.id                                                             ");
		sql.append("AND p.id = log.product_id AND y.id = "+companyid+"                                                                          ");
		sql.append("and log.product_id = "+product_id+"                                                         ");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and m.`name` like '%"+keyword+"%' ");
		}
		if(StringUtils.isNotBlank(companyname)){
			sql.append(" and y.`name` like '%"+companyname+"%' ");
		}
		if(StringUtils.isNotBlank(c_time)){
			sql.append(" and log.c_time = '"+c_time+"' ");
		}
		sql.append("group by log.product_id,log.salesman_id        ) b                                                                   ");
		Integer resultTotal = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
		
		sql = new StringBuffer();
		sql.append("SELECT log.salesman_id, m.`name` salename,y.id company_id,y.`name` company_name,log.product_id,p.`name` proname,   ");
		sql.append("p.`number` procode,log.payment_state,log.scancode_nubs nubs, CONCAT(log.product_price,'') price,CONCAT(log.product_sumprice,'') sumprice ,log.c_time ,log.payment_state,IFNULL( log.weixin_payreturn_msg,'') weixin_payreturn_msg   ");
		sql.append("FROM log_scancode_salepayment log,product p,salesman m,company y                                                     ");
		sql.append("WHERE log.salesman_id = m.id  AND m.company_id = y.id                                                             ");
		sql.append("AND p.id = log.product_id AND y.id = "+companyid+"                                                                          ");
		sql.append("and log.product_id = "+product_id+"                                                      ");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and m.`name` like '%"+keyword+"%' ");
		}
		if(StringUtils.isNotBlank(companyname)){
			sql.append(" and y.`name` like '%"+companyname+"%' ");
		}
		if(StringUtils.isNotBlank(c_time)){
			sql.append(" and log.c_time = '"+c_time+"' ");
		}
		sql.append("group by log.product_id,log.salesman_id  ");
        sql.append(" order by log.c_time desc limit ?,?  ");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sql.toString(), args);
        
        return  new Pageable<>(page, getPageSize(), resultTotal, result);
        
   }
    
    
    
    /**
     * 通过messageid更新log_scancode_salepayment表的支付状态为已支付状态
	 * @author kelan
	 * @date 2017年5月4日
	 * @param messageid
	 * @param sate
	 * @return
	 */
   public boolean updatSalesmanOffPaymentState (String messageid,String descripe,Integer sate) {
	    String sql =  "";
	    if(sate==0){//支付成功
	    	sql = "UPDATE log_scancode_salepayment SET payment_state = 1,weixin_payreturn_msg ='"+descripe+"' where message_id = ?  ";
	    }else{
	    	sql = "UPDATE log_scancode_salepayment SET payment_state = 2,weixin_payreturn_msg ='"+descripe+"' where message_id = ?  ";
	    }
		return jdbcTemplate.update(sql, messageid) == 1;
   }
   
   
   /**
     * 通过messageid查询SalesmanIdAndProductIdAndpayment_time
	 * @author kelan
	 * @date 2017年5月5日
	 * @param messageid
	 * @return
	 */
   public Map<String, Object> queryMessageidtoSalesmanIdAndProductId(String messageid){
	   String sql ="select* from log_scancode_salepayment log where log.message_id = ? ";
	   return jdbcTemplate.queryForMap(sql,messageid);
   }
   
   /**
     * 通过messageid,producrid,支付时间更新log_scancode_salesman表的支付状态为已支付状态
	 * @author kelan
	 * @date 2017年5月5日
	 * @param salesmanid
	 * @param producrid
	 * @param playmenttime
	 * @param sate
	 * @return
	 */
   public boolean updatSalesmanPaymentState (Integer salesmanid,Integer producrid,Long playmenttime,Integer sate) {
	   String sql =  "";
	   if(sate==0){//支付成功
		   sql = "UPDATE log_scancode_salesman SET payment_state = 2 where salesman_id = ? and product_id = ? and payment_time = ?    ";
	   }else{
		   sql = "UPDATE log_scancode_salesman SET payment_state = 3 where salesman_id = ? and product_id = ? and payment_time = ?  ";
	   }
	   return jdbcTemplate.update(sql, salesmanid,producrid,playmenttime) == 1;
   }
    
}